<?php
include_once('password.php');

/**
 * 
 * This class is meant to be used as a template for 
 * SQL queries, INSERTS, UPDATES, DELETES
 * @author cmondor
 *
 */
class DB {
	public $mysqliConn=false;

	//sql parameters typing
	// 	i 	corresponding variable has type integer
	// 	d 	corresponding variable has type double
	// 	s 	corresponding variable has type string
	// 	b 	corresponding variable is a blob and will be sent in packets
	const DB_INTEGER_PARAMETER_CHAR = 'i';
	const DB_DOUBLE_PARAMETER_CHAR = 'd';
	const DB_STRING_PARAMETER_CHAR = 's';
	const DB_BLOB_PARAMETER_CHAR = 'b';
	
	
	/**
	 * 
	 * When you instantite the object, it makes a connection
	 * //todo refactor into Singleton so that db connections don't constantly happen.
	 */
	public function __construct() {
		$this->init();
	}

	function init() {
		global $MY_HOST, $MY_USERNAME, $MY_PASSWORD;	
		$this->mysqliConn = new mysqli($MY_HOST, $MY_USERNAME, $MY_PASSWORD, "htgenius");
		
		$this->testConnection();
	}

	function testConnection() {
		if(mysqli_connect_error()) {
			error_log("MySQL Error during connection: " . mysqli_connect_error());
		}
	}
	
	/**
	 * 
	 * this method was inteded as a quick solution for doing sql selects.  It returns an array of associative arrays like a result set
	 * @param parameterized or raw SELECT SQL  $sql
	 * @param array of nested associative arrays array(0 => aray(paramType, value)) $paramArr
	 */
	function getSqlResults($sql, $paramArr=array()) {
		error_log(__FILE__ . " " . __LINE__ . " entering getSqlResults():" . $sql);
		for ($i=0; $i < count($paramArr); $i++) {
			$myType = $paramArr[$i][0];
			$myValue = $paramArr[$i][1];
			error_log('param ' . $i . ' ' . $myType . ' ' . $myValue);
		}
		
		$returnArr = array();
		/* create a prepared statement */
		if($stmt = $this->mysqliConn->prepare($sql)) {
			//error_log(__FILE__ . " " . __LINE__ . " after statement is prepared.");
				
			//http://code.hajime.ca/2011/06/07/dynamic-binding-of-parameters-with-mysqli-in-php/
			$types  = '';
			$params = array();
			
			//array('s_name' => array(DB::DB_STRING_PARAMETER_CHAR, "Football"));
			for ($i=0; $i < count($paramArr); $i++) {
				$types .= $paramArr[$i][0];
				$params[] = $paramArr[$i][1];
			}

			//$refArr = array("si","hello",42);
			$refArr = array($types);

			for ($i=0; $i < count($paramArr); $i++) {
				$refArr[$i + 1] = $paramArr[$i][1];
			}
			
			//error_log(__FILE__ . ":" . __LINE__ . ":");
			//print_r($refArr);
			
			# this next bit from stackoverflow
			$bind_names[] = $types;
			for ($i=0; $i<count($params);$i++) {
				$bind_name = 'bind' . $i;
				$$bind_name = $params[$i];
				$bind_names[] = &$$bind_name;
			}
			
			if(!(empty($paramArr))) {
				// Call the Function bind_param with dynamic Parameters
				call_user_func_array(array($stmt,'bind_param'), $bind_names);
			}
			//error_log(__FILE__ . " " . __LINE__ . " ");
						
			/* execute query */
			$stmt->execute();
			
			//This dynamically binds the result set to an array of associative arrays
			$meta = $stmt->result_metadata();
			$myParams = array();
			while ($field = $meta->fetch_field())
			{
				$myParams[] = &$row[$field->name];
			}
			
			call_user_func_array(array($stmt, 'bind_result'), $myParams);

			$i=0;
			while ($stmt->fetch()) {
				foreach($row as $key => $val) {
					$c[$key] = $val;
				}
				error_log(" array " . count($c));
				$returnArr[$i] = $c;
				$i++;
			}

			$stmt->close();
		} else {
			error_log(__FILE__ . " " . __LINE__ . " stmnt: " . $sql);
			error_log("params: ");
			foreach($paramArr as $key => $value) {
				error_log($key . ' => ' . $value[1] . ' : ' . $value[0]);
			}
			error_log("last error: " . $this->mysqliConn->error);
		}
		return $returnArr;
	}

	/**
	 * 
	 * This method is meant to be called just like getSqlResults
	 * however it is for inserting single records and returning 
	 * their ID or false 
	 * @param paremeterized or raw INSERT SQL string $sql
	 * @param array of nested associative arrays array(0 => aray(paramType, value)) $paramArr
	 */
	function getIdFromInsert($sql, $paramArr=array()) {
		error_log(__FILE__ . " " . __LINE__ . " entering getIdFromInsert():" . $sql);
		error_log("paramCount: " . count($paramArr));
		for ($i=0; $i < count($paramArr); $i++) {
			$myType = $paramArr[$i][0];
			$myValue = $paramArr[$i][1];
			error_log('param ' . $i . ' ' . $myType . ' ' . $myValue);
		}
		
		
		$lastInsertId=false;
		/* create a prepared statement */
		
		if($stmt = $this->mysqliConn->prepare($sql)) {
			//http://code.hajime.ca/2011/06/07/dynamic-binding-of-parameters-with-mysqli-in-php/
			$types  = '';
			$params = array();
				
			for ($i=0; $i < count($paramArr); $i++) {
				$types .= $paramArr[$i][0];
				$params[] = $paramArr[$i][1];
			}
				
			# this next bit from stackoverflow
			$bind_names[] = $types;
			for ($i=0; $i<count($params);$i++) {
			$bind_name = 'bind' . $i;
						$$bind_name = $params[$i];
			$bind_names[] = &$$bind_name;
			}
		
			if(count($params) > 0) {
				call_user_func_array(array($stmt,'bind_param'),$bind_names);
			}
		
			/* execute query */
			$stmt->execute();
			
			if($stmt->error || $this->mysqliConn->error) {
				error_log(__FILE__ . " " . __LINE__ . " inside DB.getIdFromInsert() -- MySQL Error when trying to insert: " . $stmt->error . ' ' . $sql);
				error_log("mysql last error: " . $this->mysqliConn->error);
				error_log(__FILE__ . " " . __LINE__ . "stmnt: " . $sql);
				error_log("params: ");
				foreach($paramArr as $key => $value) {
					error_log($key . ' => ' . $value[1] . ' : ' . $value[0]);
				}
				
				return false; 
			}
			error_log(__FILE__ . " " . __LINE__ . " inside DB.getIdFromInsert() -- MySQL Error when trying to insert: " . $stmt->error  . ' ' . $sql);
			$lastInsertId = $stmt->insert_id;
			
		} else {
			error_log(__FILE__ . " " . __LINE__ . "stmnt: " . $sql);
			error_log("params: ");
			foreach($paramArr as $key => $value) {
				error_log($key . ' => ' . $value[1] . ' : ' . $value[0]);
			}

			error_log("last error: " . $this->mysqliConn->error);
		}

		return $lastInsertId;
	}
	
}
?>